package com.llh.controller.processdata;

import com.llh.bean.FinalData;
import com.llh.bean.Pe;
import com.llh.controller.processdata.computVo.BasicYearVo;
import com.llh.mapper.FinalDataMapper;
import com.llh.mapper.PeMapper;
import com.llh.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ComputeBasic_Year {
    static Logger logger = LogManager.getLogger(ComputeBasic_Year.class.getName());
    public static void computeYearData() throws Exception{
        SqlSession sqlSession = null;
        sqlSession = SqlSessionUtil.getSqlSesion();
        FinalDataMapper mapper = sqlSession.getMapper(FinalDataMapper.class);
        List<FinalData> all = mapper.findAll();
        sqlSession.close();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
        List<BasicYearVo> basicYearVoList = new ArrayList<>();
        int num = 0;
        for (FinalData finalData : all) {
            BasicYearVo basicYearVo = new BasicYearVo();
            basicYearVo.setS_code(finalData.getS_code());
            basicYearVo.setS_name(finalData.getS_name());
            basicYearVo.setOpen_day(finalData.getOpen_day());
            basicYearVo.setEnd_date(finalData.getEnd_date());
            sqlSession = SqlSessionUtil.getSqlSesion();
            PeMapper mapper1 = sqlSession.getMapper(PeMapper.class);
            logger.info("第"+(++num)+"次获取vo");
            Pe pe = mapper1.findByS_code(finalData);
            sqlSession.close();
            logger.info("关闭session");
            //解禁日上一季度报告期
            String endDates = format.format(finalData.getEnd_date());
            //解禁日
            String open_Days = format.format(finalData.getOpen_day());
            Integer integer = Integer.valueOf(open_Days);
            //解禁日前一年
            int year = (integer/10000)-1;
            switch (endDates) {
                case "20210930":
                    basicYearVo.setMarket_value(pe.getMv20210930());
                    break;
                case "20210630":
                    basicYearVo.setMarket_value(pe.getMv20210630());
                    break;
                case "20210331":
                    basicYearVo.setMarket_value(pe.getMv20210331());
                    break;
                case "20201231":
                    basicYearVo.setMarket_value(pe.getMv20201231());
                    break;
                case "20200930":
                    basicYearVo.setMarket_value(pe.getMv20200930());
                    break;
                case "20200630":
                    basicYearVo.setMarket_value(pe.getMv20200630());
                    break;
                case "20200331":
                    basicYearVo.setMarket_value(pe.getMv20200331());
                    break;
                case "20191231":
                    basicYearVo.setMarket_value(pe.getMv20191231());
                    break;
                case "20190930":
                    basicYearVo.setMarket_value(pe.getMv20190930());
                    break;
                case "20190630":
                    basicYearVo.setMarket_value(pe.getMv20190630());
                    break;
                case "20190331":
                    basicYearVo.setMarket_value(pe.getMv20190331());
                    break;
                default:
                    basicYearVo.setMarket_value(null);
            }
            basicYearVo.setPre_Year(year+"");
            switch (year){
                case 2018:
                    basicYearVo.setPe(pe.getPe18());
                    break;
                case 2019:
                    basicYearVo.setPe(pe.getPe19());
                    break;
                case 2020:
                    basicYearVo.setPe(pe.getPe20());
                    break;
                default:
                    basicYearVo.setPe(null);
            }
            basicYearVoList.add(basicYearVo);
        }
        //批量导出
        File in = new File("C:\\Users\\llh\\Desktop\\股票基本信息(年度信息)表头.xlsx");
        File out = new File("C:\\Users\\llh\\Desktop\\股票基本信息(年度信息)导表.xlsx");
        writeExcel(in,out,1,basicYearVoList);

    }

    //创建表头
    public static void creatExcel() throws Exception {
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
        SXSSFSheet sheet = sxssfWorkbook.createSheet("基本数据");
        SXSSFRow row = sheet.createRow(0);
        SXSSFCell cell0_0 = row.createCell(0);
        cell0_0.setCellValue("股票代码");
        SXSSFCell cell0_1 = row.createCell(1);
        cell0_1.setCellValue("名称");
        SXSSFCell cell0_2 = row.createCell(2);
        cell0_2.setCellValue("解禁日");
        SXSSFCell cell0_3 = row.createCell(3);
        cell0_3.setCellValue("上一季度报告期");
        SXSSFCell cell0_4 = row.createCell(4);
        cell0_4.setCellValue("上一年度");
        SXSSFCell cell0_5 = row.createCell(5);
        cell0_5.setCellValue("解禁日上季度市值");
        SXSSFCell cell0_6 = row.createCell(6);
        cell0_6.setCellValue("解禁日上年度市盈率");
      /*  cell0_7.setCellValue("报告期去年同期");
        SXSSFCell cell0_8 = row.createCell(8);
        cell0_8.setCellValue("净资产收益率(上一季度)");
        SXSSFCell cell0_9 = row.createCell(9);
        cell0_9.setCellValue("资产负债率(上一季度)");
        SXSSFCell cell0_10 = row.createCell(10);
        cell0_10.setCellValue("同比增长率:营业收入");
        SXSSFCell cell0_11 = row.createCell(11);
        cell0_11.setCellValue("同比增长率:归属母公司股东的净利润");*/
        FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\llh\\Desktop\\股票基本信息(年度信息)表头.xlsx"));
        sxssfWorkbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        sxssfWorkbook.close();
        System.out.println("结束");
    }

    //写数据
    public static void writeExcel(File in, File out, int startRow,  List<BasicYearVo> basicYearVoList) throws Exception {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        FileInputStream fileInputStream = null;
        XSSFWorkbook workbook1 = null;
        SXSSFWorkbook sxssfWorkbook = null;
        FileOutputStream fileOutputStream = null;
        try {
            fileInputStream = new FileInputStream(in);
            workbook1 = new XSSFWorkbook(fileInputStream);
            sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
            SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
            if(basicYearVoList == null || basicYearVoList.size()==0){
                System.out.println("没要要导的数据结束");
                return;
            }
            for (BasicYearVo basicYearVo : basicYearVoList) {
                SXSSFRow row = sheet.createRow(startRow);
                SXSSFCell cell0_0 = row.createCell(0);
                cell0_0.setCellValue(basicYearVo.getS_code());
                SXSSFCell cell0_1 = row.createCell(1);
                cell0_1.setCellValue(basicYearVo.getS_name());
                SXSSFCell cell0_3 = row.createCell(2);
                cell0_3.setCellValue(basicYearVo.getOpen_day());
                SXSSFCell cell0_4 = row.createCell(3);
                cell0_4.setCellValue(basicYearVo.getEnd_date());
                SXSSFCell cell0_5 = row.createCell(4);
                cell0_5.setCellValue(basicYearVo.getPre_Year());
                SXSSFCell cell0_6 = row.createCell(5);
                cell0_6.setCellValue(basicYearVo.getMarket_value());
                SXSSFCell cell0_7 = row.createCell(6);
                cell0_7.setCellValue(basicYearVo.getPe());
                /*SXSSFCell cell0_8 = row.createCell(7);
                cell0_8.setCellValue(finalData.getPreyear_end_date());
                SXSSFCell cell0_2 = row.createCell(8);
                cell0_2.setCellValue(finalData.getRoe());
                SXSSFCell cell0_9 = row.createCell(9);
                //小数位原格式
                cell0_9.setCellValue(finalData.getDebt_to_assets());
                SXSSFCell cell0_10 = row.createCell(10);
                //小数位原格式
                cell0_10.setCellValue(finalData.getYoy_sales());
                SXSSFCell cell0_11 = row.createCell(11);
                //小数位原格式
                cell0_11.setCellValue(finalData.getYoy_dedu_np());*/

/*//                cell.setCellValue(new BigDecimal(data.toString()).setScale(4, BigDecimal.ROUND_HALF_UP).toString());

                    //导真实数据
                    Double data = daily_hzs.get(i).getData();
                    if(data==null){
                        cell.setCellValue("");
                    }else{
//                        cell.setCellValue(daily_hzs.get(i).getData());
                        cell.setCellValue(new BigDecimal(data.toString()).setScale(4, BigDecimal.ROUND_HALF_UP).toString());
                    }
                }*/
                System.out.println("插入第" + startRow + "行数据完成");
                startRow++;
            }
            fileOutputStream = new FileOutputStream(out);
            sxssfWorkbook.write(fileOutputStream);
            fileOutputStream.flush();


        } finally {
            fileInputStream.close();
            fileOutputStream.close();
            sxssfWorkbook.close();
        }
    }

    public static void main(String[] args) throws Exception {
//        creatExcel();
//        System.out.println("表头创建成功");
        computeYearData();
        System.out.println("导表结束");
    }
}
